create temporary function getDueTime as 'com.yunlu.bigdata.function.udf.GetDueTime' using jar 'hdfs:///user/wangshengya/udf/getduetime.jar';

with false_sign_order as (select waybill_no,                                                              -- 运单号 string 1
                                 work_order_no,                                                           -- 工单编号 string 2
                                 create_time,                                                             --登记时间 timestamp 3
                                 network_code,                                                            --转单责任网点编码 string 4
                                 network_name,                                                            --转单责任网点名称 string 5
                                 delivery_code,                                                           --派件员编码 string 6
                                 delivery_name,                                                           --派件员名称 string 7
                                 customer_type,                                                           --客户类型 1:寄件人 2:收件人 3:其他  tinyint 8
                                 deliver_type,                                                            --投递类型(物流轨迹,最新一条入库扫描中的remark1) string 9
                                 second_type_code,                                                        --二级类型code string 10
                                 second_type_name,                                                        --二级类型name string 11
                                 problem_desc                                        problem_description, --问题描述string 12
                                 dt,                                                                      --分区
                                 case when work_type = '普通工单'
                                      then customer_type
                                      else work_order_source_id
                                 end                                                  complaint_target,    --工单source
                                 if(work_type = '普通工单', '8', work_order_channel)   work_order_channel,
                                 send_type_address,                                                       --投递地址
                                 outstore_later_scantime                                       outstore_early_scantime,
                                 sign_later_scantime,
                                 instore_later_scantime                              instore_early_scantime,
                                 if(work_type = '普通工单',2,1)                       work_type,
                                 close_time ,
                                 last_reply
                          from jms_dm.dm_waybill_customer_complaint_detail_new_dt
                          where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01') and dt <= '{{ execution_date | cst_ds }}'
                            and is_delete = 2 --未删除
                            and network_code <> '888888' --剔除总部
                            and ((work_type = '普通工单' and second_type_code = '2104') or (work_type = '平台工单'))),
     net_order as (
          select detail.*,
                 case when net.provider_id = '650000' then getDueTime(detail.create_time,'10:00','19:30')  --新疆
                      when net.provider_id = '540000' then getDueTime(detail.create_time,'09:30','19:30')  --西藏
                 else getDueTime(detail.create_time,'08:30','19:30')
                 end reply_due_time   --正常省份
          from false_sign_order detail
          left join jms_dim.dim_network_whole_massage net
          on detail.waybill_no = net.code
     ),
     tab_sign as (select *
                  from (select a.waybill_no,
                               a.signed_type                                                             sign_type,
                               row_number() over (partition by a.waybill_no order by a.input_time desc ) rn
                        from jms_dwd.dwd_tab_barscan_sign_base_dt a
                                 left semi
                                 join
                             false_sign_order b on a.waybill_no = b.waybill_no
                        where dt >= date_add(date('{{ execution_date | cst_ds }}'), -60 + 1)
                          and dt <= '{{ execution_date | cst_ds }}') detail
                  where rn = 1),
     refund as (select a.waybill_no
                from jms_dwd.dwd_tab_reback_transfer_express_base_dt a
                         left semi
                         join
                     false_sign_order b on a.waybill_no = b.waybill_no
                where dt >= date_add(date('{{ execution_date | cst_ds }}'), -60 + 1)
                  and dt <= '{{ execution_date | cst_ds }}'
                  and a.STATUS not in ('3', '4') --面单是否已打印 1是 0否
                  and a.APPLY_TYPE_CODE != '5'   --面单是否已打印 1是 0否
                group by waybill_no),
     order as (select *
               from (select a.waybill_id,
                            a.customer_name,
                            a.customer_code,
                            a.input_time,
                            row_number() over (partition by a.waybill_id order by a.input_time ) rn
                     from jms_dwd.dwd_yl_oms_oms_order_incre_dt a
                              left semi
                              join
                          false_sign_order b on a.waybill_id = b.waybill_no
                     where dt >= date_add(date('{{ execution_date | cst_ds }}'), -60 + 1)
                       and dt <= '{{ execution_date | cst_ds }}') detail
               where rn = 1),
     performance as (select a.waybill_id, max(a.is_contact) is_contact
                     from jms_dwd.dwd_oms_order_performance_main_base_dt a
                              left semi
                              join
                          false_sign_order b on a.waybill_id = b.waybill_no
                     where dt >= date_add(date('{{ execution_date | cst_ds }}'), -60 + 1)
                       and dt <= '{{ execution_date | cst_ds }}'
                     group by WAYBILL_ID)


insert
overwrite
table
jms_dm.dm_sqs_false_sign_detail_dt
partition
(
dt
)
select date(work.create_time)                                                   statistic_date,
       work.waybill_no,
       work.work_order_no,
       network.agent_code,
       work.delivery_code,
       work.network_code,
       network.name                                                             network_name,
       network.network_type,
       network.agent_name,
       nvl(network.virt_code, network.agent_code)                               virt_code,
       nvl(network.virt_name, network.agent_name)                               virt_name,
       order.customer_code,
       order.customer_name,
       order.input_time,
       network.zone_id                                                          area_info_id,
       network.zone_code                                                        area_info_code,
       network.zone_name                                                        area_info_desc,
       network.provider_id,
       network.provider_desc,
       network.city_id,
       network.city_desc,
       network.area_id,
       network.area_desc,
       network.is_enable,
       network.is_suspend,
       work.delivery_name,
       work.create_time,
       work.work_type,
       cast(work.second_type_code as string)                                    second_type_code,
       cast(work.second_type_name as string)                                    second_type_name,
       work.problem_description,
       work.customer_type,
       work.deliver_type,
       tab_sign.sign_type,
       if(refund.waybill_no is not null, 1, 0)                                  is_reback,
       if(performance.waybill_id is not null, 1, 0)                             is_phone_contact,
       nvl(performance.is_contact, 0)                                           is_promise,
       work.instore_early_scantime                                          instore_scantime,
       work.outstore_early_scantime                                         outstore_scantime,
       case
           when if(refund.waybill_no is not null, 1, 0) = 1 then 1
           when if(refund.waybill_no is not null, 1, 0) = 0 and nvl(deliver_type, '') <> '' then
               if(work.create_time >= work.outstore_early_scantime, 5, 6)
           when if(refund.waybill_no is not null, 1, 0) = 0 and nvl(deliver_type, '') = '' and
                sign_type REGEXP '家人|本人|朋友|同事|亲属|同学|前台|门口' then 3
           else 4 end                                                           false_type,
       complaint_target,
       work_order_channel,
       if(work.problem_description = '用户表示"未收到货"，请联系用户处理', 1, 0) is_cainiao_enter,
       unix_timestamp(
               work.outstore_early_scantime) -
       unix_timestamp(
               work.instore_early_scantime)                                     inout_time_diff,
       network.area_code                                                        area_code,
       network.area_name                                                        area_name,
       network.leaf_area_id                                                     sys_area_id,
       send_type_address,
       case when dispatcher_work_order.is_reply = 1 then 1
            when dispatcher_work_order.is_reply = 2 then 0
            else null end is_reply,
       dispatcher_work_order.last_reply,
       dispatcher_work_order.reply_time,
       case when dispatcher_work_order.reply_time is null then null
            when dispatcher_work_order.reply_time > work.reply_due_time then 1
            when dispatcher_work_order.reply_time <= work.reply_due_time
             and dispatcher_work_order.reply_time >= work.create_time then 0
       else null end is_reply_timeout,
       nvl(work.last_reply,dispatcher_work_order.close_opinions) order_close_handling_opinions,
       work.close_time order_close_handling_time,
       if(dispatcher_work_order.waybill_no is not null ,1 , 0 ) is_dispatcher_order,
       reply_due_time,
       dt
from net_order work
         left join tab_sign on
    work.waybill_no = tab_sign.waybill_no
         left join refund on
    work.waybill_no = refund.waybill_no
         left join order on
    work.waybill_no = order.waybill_id
         left join performance on
    work.waybill_no = performance.waybill_id
         left join jms_dim.dim_network_whole_massage network
    on network.code = work.network_code
         left join (
             select work_order_no
                   ,waybill_no
                   ,last_reply
                   ,is_reply
                   ,reply_time
                   ,close_opinions
                   ,row_number() over(partition by work_order_no order by create_time ) rn
             from jms_dwd.dwd_dispatcher_work_order_base_dt where dt >= date_add(concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01'), -1) and dt <= '{{ execution_date | cst_ds }}'
         ) dispatcher_work_order
    on dispatcher_work_order.work_order_no = work.work_order_no
    and dispatcher_work_order.waybill_no = work.waybill_no
    and dispatcher_work_order.rn = 1
    distribute by dt
   , 1;
